/*===========================================================================+
 |   Copyright (c) 2001, 2005 Oracle Corporation, Redwood Shores, CA, USA    |
 |                         All rights reserved.                              |
 +===========================================================================+
 |  HISTORY                                                                  |
 +===========================================================================*/
package sza.oracle.apps.ap.oie.poimport.webui;


import com.sun.java.util.collections.HashMap;

import java.io.Serializable;

import java.sql.CallableStatement;
import java.sql.Types;

import java.util.ArrayList;
import java.util.List;

import oracle.apps.ap.oie.webui.NavigationUtility;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAInnerDataObjectEnumerator;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;

import oracle.apps.fnd.framework.webui.beans.message.OAMessageTextInputBean;
import oracle.apps.fnd.framework.webui.beans.table.OAAdvancedTableBean;
import oracle.apps.fnd.framework.webui.beans.table.OATableBean;

import oracle.jbo.Row;

import oracle.jbo.RowSet;

import oracle.jbo.server.DBTransaction;

import sza.oracle.apps.ap.oie.poimport.server.PoImportInvoiceLineVOImpl;
import sza.oracle.apps.ap.oie.poimport.server.PoImportInvoiceVOImpl;
import sza.oracle.apps.ap.oie.poimport.server.PoImportInvoiceVORowImpl;

/**
 * Controller for ...
 */
public class PoImportCO extends OAControllerImpl {
    public static final String RCS_ID = "$Header$";
    public static final boolean RCS_ID_RECORDED = 
        VersionInfo.recordClassVersion(RCS_ID, "%packagename%");

    /**
     * Layout and page setup logic for a region.
     * @param pageContext the current OA page context
     * @param webBean the web bean corresponding to the region
     */
    public void processRequest(OAPageContext pageContext, OAWebBean webBean) {
        super.processRequest(pageContext, webBean);
        OAApplicationModule am = 
            (OAApplicationModule)pageContext.getApplicationModule(webBean);

        OAWebBean innerTable = 
            (OAWebBean)webBean.findChildRecursive("InvoiceLineTableTB");
        //      OAWebBean outerTable = (OAWebBean)webBean.findChildRecursive("InvoiceTableTB"); 
        //      if (outerTable != null) 
        //      { 
        //        outerTable.setAttributeValue(CHILD_VIEW_ATTRIBUTE_NAME,"InvoiceId"); 
        //        outerTable.setAttributeValue(VIEW_LINK_NAME,"InvoiceHeaderToLineVL1"); 
        //      } 
        if (innerTable != null) {
            innerTable.setAttributeValue(CHILD_VIEW_ATTRIBUTE_NAME, 
                                         "InvoiceId");
            innerTable.setAttributeValue(VIEW_LINK_NAME, 
                                         "InvoiceHeaderToLineVL1");
        }
        String selectExpType = 
            pageContext.getParameter("SelectedTreeNodeValue");

        String invoiceNum = pageContext.getParameter("InvoiceNum");
            Serializable[] parameters = { invoiceNum };
            am.invokeMethod("initInvoice", parameters);
       
    }

    public void setSelectedExpType(OAPageContext pageContext, 
                                   OAWebBean webBean, String selectExpType) {
        String invoiceId = pageContext.getSessionValue("INVOICE_ID") + "";
        String lineNumber = pageContext.getSessionValue("LINE_NUMBER") + "";
        List rowList = 
            getRowInInnerTable(invoiceId, lineNumber, pageContext, webBean);
        for (int i = 0; i < rowList.size(); i++) {
            Row row = (Row)rowList.get(i);
            row.setAttribute("ItemDescription", selectExpType);
        }

    }

    /**
     * Procedure to handle form submissions for form elements in
     * a region.
     * @param pageContext the current OA page context
     * @param webBean the web bean corresponding to the region
     */
    public void processFormRequest(OAPageContext pageContext, 
                                   OAWebBean webBean) {
        super.processFormRequest(pageContext, webBean);

        OAApplicationModule am = pageContext.getApplicationModule(webBean);

        if ("Select".equals(pageContext.getParameter("event")) == true) {


            String invoiceId = pageContext.getParameter("InvoiceId");
            String lineNumber = pageContext.getParameter("LineNumber");
            HashMap hashmap = new HashMap();
            hashmap.put("SDP_RHID", 
                        NavigationUtility.encrypt(pageContext, "12345"));

            hashmap.put("SDP_RLID", 
                        NavigationUtility.encrypt(pageContext, "1"));


            hashmap.put("CashDistLineNumber", 
                        NavigationUtility.encrypt(pageContext, "1"));

            hashmap.put("SDP_EXP_CLASS", "SDP_EXP_CLASS_CASH");

            hashmap.put("SDP_UPDATE_MODE", "UPDATE");

            hashmap.put("SDP_CALLING_MODULE", "SZA_CASH_ET");

            hashmap.put("$$FWK_MultiOrgCurrentOrgId", "81");
            hashmap.put("TemplateId", "10025");
            hashmap.put("EmployeeId", "14394");
            hashmap.put("DefaultCostCenter", "1010300");

            hashmap.put("retainAM", "Y");
            hashmap.put("SDP_SHOW_ERRORS", "SDP_SHOW_ERRORS");
            pageContext.putSessionValue("INVOICE_ID", invoiceId);
            pageContext.putSessionValue("LINE_NUMBER", lineNumber);
            StringBuffer localStringBuffer = new StringBuffer();
            localStringBuffer.append("OA.jsp?page=");
            localStringBuffer.append("/sza/oracle/apps/ap/oie/webui/ExpenseTypeTreePG");
            pageContext.forwardImmediately(localStringBuffer.toString(), 
                                           "OIEENTRYFLOW", (byte)0, null, 
                                           hashmap, true, "N");

        } else if (pageContext.getParameter("ImportBtn") != null) {
            String reportHeaderId = "";
            PoImportInvoiceVOImpl vo = 
                (PoImportInvoiceVOImpl)am.findViewObject("PoImportInvoiceVO1");
            if (vo.getFetchedRowCount() <= 0) {
                throw new OAException("SQLSZA", "KMA_MUST_SELECT_ONE_ITEM");
            }
            Row[] selectedRows = 
                vo.getFilteredRowsInFetchedRange("Selected", "Y");
            if (selectedRows.length <= 0) {
                throw new OAException("SQLSZA", "KMA_MUST_SELECT_ONE_ITEM");
            }

            String str = 
                "BEGIN SZA_OIE_PO_IMPORT_PKG.submit_work_flow(p_report_header_id => :1 );END; ";

            for (int i = 0; i < selectedRows.length; i++) {
                Row row = selectedRows[i];
                reportHeaderId = row.getAttribute("ReportHeaderId") + "";
                System.err.println("Select reportHeaderId :" + reportHeaderId);
                CallableStatement localCallableStatement = 
                    am.getOADBTransaction().createCallableStatement(str, 1);
                try {
                    localCallableStatement.setString(1, reportHeaderId);
                    localCallableStatement.execute();
                    am.getOADBTransaction().commit();
                }

                catch (Exception localException3) {
                    localException3.printStackTrace();
                    throw OAException.wrapperException(localException3);
                } finally {
                    try {
                        localCallableStatement.close();
                    } catch (Exception localException4) {
                        throw OAException.wrapperException(localException4);
                    }
                }
            }

        } else if (pageContext.getParameter("CancelBtn") != null) {
            String reportHeaderId = "";
            PoImportInvoiceVOImpl vo = 
                (PoImportInvoiceVOImpl)am.findViewObject("PoImportInvoiceVO1");
            if (vo.getFetchedRowCount() <= 0) {
                throw new OAException("SQLSZA", "KMA_MUST_SELECT_ONE_ITEM");
            }
            Row[] selectedRows = 
                vo.getFilteredRowsInFetchedRange("Selected", "Y");
            if (selectedRows.length <= 0) {
                throw new OAException("SQLSZA", "KMA_MUST_SELECT_ONE_ITEM");
            }

            String str = 
                "BEGIN SZA_OIE_PO_IMPORT_PKG.cancel_expense(p_report_header_id => :1 );END; ";

            for (int i = 0; i < selectedRows.length; i++) {
                Row row = selectedRows[i];
                reportHeaderId = row.getAttribute("ReportHeaderId") + "";
                System.err.println("Select reportHeaderId :" + reportHeaderId);
                CallableStatement localCallableStatement = 
                    am.getOADBTransaction().createCallableStatement(str, 1);
                try {
                    localCallableStatement.setString(1, reportHeaderId);
                    localCallableStatement.execute();
                    am.getOADBTransaction().commit();
                }

                catch (Exception localException3) {
                    localException3.printStackTrace();
                    throw OAException.wrapperException(localException3);
                } finally {
                    try {
                        localCallableStatement.close();
                    } catch (Exception localException4) {
                        throw OAException.wrapperException(localException4);
                    }
                }
            }

        } else if ("withdraw".equals(pageContext.getParameter(EVENT_PARAM))) {
            String reportHeaderId = pageContext.getParameter("reportHeaderId");
            String str = 
                "BEGIN SZA_OIE_PO_IMPORT_PKG.WithdrawExpenseRep(p_rep_header_id => :1 );END; ";

            CallableStatement localCallableStatement = 
                am.getOADBTransaction().createCallableStatement(str, 1);
            try {
                localCallableStatement.setInt(1, 
                                              new Integer(reportHeaderId).intValue());
                localCallableStatement.execute();
                am.getOADBTransaction().commit();
            }

            catch (Exception localException3) {
                localException3.printStackTrace();
                throw OAException.wrapperException(localException3);
            } finally {
                try {
                    localCallableStatement.close();
                } catch (Exception localException4) {
                    throw OAException.wrapperException(localException4);
                }
            }
        } else if ("delete".equals(pageContext.getParameter(EVENT_PARAM))) {
            String reportHeaderId = pageContext.getParameter("reportHeaderId");
            String str = 
                "BEGIN AP_WEB_OA_MAINFLOW_PKG.DeleteExpenseReport(ReportID => :1 );END; ";

            CallableStatement localCallableStatement = 
                am.getOADBTransaction().createCallableStatement(str, 1);
            try {
                localCallableStatement.setInt(1, 
                                              new Integer(reportHeaderId).intValue());
                localCallableStatement.execute();
                am.getOADBTransaction().commit();
            }

            catch (Exception localException3) {
                localException3.printStackTrace();
                throw OAException.wrapperException(localException3);
            } finally {
                try {
                    localCallableStatement.close();
                } catch (Exception localException4) {
                    throw OAException.wrapperException(localException4);
                }
            }
        } else if("query".equals(pageContext.getParameter(EVENT_PARAM))){
            String invoiceNum = pageContext.getParameter("InvoiceNum")+"";
            System.out.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@invoice number is:"+invoiceNum);
            Serializable[] parameters = { invoiceNum };
            am.invokeMethod("initInvoice", parameters);
        }
        pageContext.forwardImmediatelyToCurrentPage(null,true,null);
    }

    private void importHeader(Row[] selectedRows, OAApplicationModule am, 
                              OAPageContext pageContext, OAWebBean webBean) {

        String reportHeaderId = "";
        String errMsg = "";
        String callStmt = 
            " begin SZA_OIE_PO_IMPORT_PKG.import_header_to_oie(p_invoice_date => :1,\n" + 
            "                          p_vouchno       => :2,\n" + 
            "                          p_invoice_amount  => :3,\n" + 
            "                          p_vendor_id       => :4,\n" + 
            "                          p_vendor_site_id  => :5,\n" + 
            "                          p_set_of_book_id  => :6,\n" + 
            "                          p_description     => :7,\n" + 
            "                          p_attribute6     => :8,\n" + 
            "                          p_attribute8      => :9,\n" + 
            "                          p_attribute10      => :10,\n" + 
            "                          p_attribute11      => :11,\n" + 
            "                          p_attribute13     => :12,\n" + 
            "                          p_attribute15     => :13,\n" + 
            "                          p_default_currency_code  => :14,\n" + 
            "                          p_default_exchange_rate_type => :15,\n" + 
            "                          p_default_exchange_rate       => :16,\n" + 
            "                          p_default_exchange_date       => :17,\n" + 
            "                          p_payment_currency_code      => :18,\n" + 
            "                          p_payment_cross_rate_type     => :19,\n" + 
            "                          p_payment_cross_rate_date    => :20,\n" + 
            "                          p_payment_cross_rate          => :21,\n" + 
            "                          p_org_id                     => :22,\n" + 
            "                          o_msg_message       =>:23,\n" + 
            "                          o_report_header_id => :24); end;";
        DBTransaction txn = am.getOADBTransaction();
        System.err.println(callStmt);
        CallableStatement cs = null;
        System.err.println("selectedRows.length is:" + selectedRows.length);
        PoImportInvoiceVORowImpl row = null;
        for (int i = 0; i < selectedRows.length; i++) {

            row = (PoImportInvoiceVORowImpl)selectedRows[i];

            try {
                cs = txn.createCallableStatement(callStmt, 1);
                cs.setString(1, row.getInvoiceDate() + "");
                cs.setString(2, row.getInvoiceId() + "");
                cs.setString(3, row.getInvoiceAmount() + "");
                cs.setString(4, 
                             row.getVendorId() == null ? "" : row.getVendorId() + 
                             "");
                cs.setString(5, 
                             row.getVendorSiteId() == null ? "" : row.getVendorSiteId() + 
                             "");
                cs.setString(6, row.getSetOfBooksId() + "");
                cs.setString(7, row.getDescription());
                cs.setString(8, row.getVendorSitId());
                cs.setString(9, null);
                cs.setString(10, row.getAttachedFileCount() + "");
                cs.setString(11, row.getPhoneNumber());
                cs.setString(12, null);
                cs.setString(13, row.getFirstApprover());
                cs.setString(14, row.getInvoiceCurrencyCode());
                cs.setString(15, row.getExchangeRateType());
                cs.setString(16, 
                             row.getExchangeRate() == null ? "" : row.getExchangeRate() + 
                             "");
                cs.setString(17, 
                             row.getExchangeDate() == null ? "" : row.getExchangeDate() + 
                             "");
                cs.setString(18, row.getPaymentCurrencyCode());
                cs.setString(19, row.getPaymentCrossRateType());
                cs.setString(20, row.getPaymentCrossRateDate() + "");
                cs.setString(21, row.getPaymentCrossRate() + "");
                //cs.setString(22, row.getOrgId() + "");
                cs.setString(22, pageContext.getOrgId() + "");
                cs.setString(23, errMsg);
                cs.setString(24, reportHeaderId);
                cs.registerOutParameter(23, Types.VARCHAR);
                cs.registerOutParameter(24, Types.VARCHAR);
                cs.execute();
                errMsg = cs.getString(23);
                reportHeaderId = cs.getString(24);
                System.err.println("report header id:" + reportHeaderId);
                if (!"".equals(errMsg) && errMsg != null) {
                    throw new OAException(errMsg);
                }
                cs.close();
            } catch (Exception e) {
                e.printStackTrace();
                try {
                    cs.close();
                } catch (Exception ex) {

                }
            }

            importLine(row.getInvoiceId() + "", row.getInvoiceNum(), 
                       row.getOrgId() + "", reportHeaderId, pageContext, 
                       webBean, am);
            submitToApproval(reportHeaderId, row, pageContext, am);

        }


    }

    private void importLine(String invoiceId, String invoiceNumber, 
                            String orgId, String reportHeaderId, 
                            OAPageContext pageContext, OAWebBean webBean, 
                            OAApplicationModule am) {
        List lineList = 
            getRowInInnerTable(invoiceId, null, pageContext, webBean);
        System.err.println("LineList.size():" + lineList.size());
        String reportLineId = "";
        String errMsg = "";
        String callStmt = 
            " begin SZA_OIE_PO_IMPORT_PKG.import_line_to_oie(p_report_header_id => :1,\n" + 
            "                          p_invoice_id       => :2,\n" + 
            "                          p_invoice_number  => :3,\n" + 
            "                          p_line_number       => :4,\n" + 
            "                          p_tree_node_code  => :5,\n" + 
            "                          p_org_id  => :6,\n" + 
            "                          o_msg_message     => :7,\n" + 
            "                          o_report_line_id => :8); end;";
        DBTransaction txn = am.getOADBTransaction();
        System.err.println(callStmt);
        CallableStatement cs = null;
        Row row = null;
        for (int i = 0; i < lineList.size(); i++) {
            row = (Row)lineList.get(i);

            try {
                cs = txn.createCallableStatement(callStmt, 1);
                cs.setString(1, reportHeaderId);
                cs.setString(2, invoiceId);
                cs.setString(3, invoiceNumber);
                cs.setString(4, row.getAttribute("LineNumber") + "");
                cs.setString(5, row.getAttribute("ItemDescription") + "");
                cs.setString(6, orgId);
                cs.setString(7, errMsg);
                cs.setString(8, reportLineId);
                cs.registerOutParameter(7, Types.VARCHAR);
                cs.registerOutParameter(8, Types.VARCHAR);
                cs.execute();
                errMsg = cs.getString(7);
                reportLineId = cs.getString(8);
                if (!"".equals(errMsg) && errMsg != null) {
                    throw new OAException(errMsg);
                }
                cs.close();
            } catch (Exception e) {
                e.printStackTrace();
                try {
                    cs.close();
                } catch (Exception ex) {

                }
            }

        }
    }

    private List getRowInInnerTable(String invoiceId, String lineNumber, 
                                    OAPageContext pageContext, 
                                    OAWebBean webBean) {

        OAAdvancedTableBean innerTable = 
            (OAAdvancedTableBean)webBean.findChildRecursive("InvoiceLineTableTB");

        // create an enumerator
        OAInnerDataObjectEnumerator enums = 
            new OAInnerDataObjectEnumerator(pageContext, innerTable);
        List returnValue = new ArrayList();
        while (enums.hasMoreElements()) {
            RowSet innerRowSet = (RowSet)enums.nextElement();

            // get all rows 
            Row[] rowsInRange = innerRowSet.getAllRowsInRange();
            for (int i = 0; i < rowsInRange.length; i++) {
                Row nextRow = (Row)rowsInRange[i];
                if (invoiceId.equals(nextRow.getAttribute("InvoiceId") + "")) {
                    if ("".equals(lineNumber) || lineNumber == null) {
                        returnValue.add(nextRow);
                    } else if (lineNumber.equals(nextRow.getAttribute("LineNumber") + 
                                                 "")) {
                        returnValue.add(nextRow);
                    }
                }
            }


        }
        return returnValue;
    }


    public void ImportToOIE(OAApplicationModule am, OAPageContext pageContext, 
                            OAWebBean webBean) {
        PoImportInvoiceVOImpl vo = 
            (PoImportInvoiceVOImpl)am.findViewObject("PoImportInvoiceVO1");
        if (vo.getFetchedRowCount() <= 0) {
            throw new OAException("SQLSZA", "KMA_MUST_SELECT_ONE_ITEM");
        }
        Row[] selectedRows = vo.getFilteredRowsInFetchedRange("Selected", "Y");
        if (selectedRows.length <= 0) {
            throw new OAException("SQLSZA", "KMA_MUST_SELECT_ONE_ITEM");
        }
        importHeader(selectedRows, am, pageContext, webBean);

    }

    private void submitToApproval(String reportHeaderId, 
                                  PoImportInvoiceVORowImpl row, 
                                  OAPageContext pageContext, 
                                  OAApplicationModule am) {
        String outCount = "";
        String errMsg = "";
        String callStmt = 
            " begin SZA_OIE_PO_IMPORT_PKG.submit_work_flow(p_report_header_id => :1,\n" + 
            "                          p_employee_id       => :2,\n" + 
            "                          o_count  => :3,\n" + 
            "                          o_err_msg => :4); end;";
        DBTransaction txn = am.getOADBTransaction();
        System.err.println(callStmt);
        CallableStatement cs = null;

        try {
            cs = txn.createCallableStatement(callStmt, 1);
            cs.setString(1, reportHeaderId);
            cs.setString(2, pageContext.getEmployeeId() + "");

            cs.registerOutParameter(3, Types.VARCHAR);
            cs.registerOutParameter(4, Types.VARCHAR);
            cs.execute();
            outCount = cs.getString(3);
            errMsg = cs.getString(4);
            System.err.println("errMsg:" + errMsg);
            System.err.println("outCount" + outCount);
            cs.close();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                cs.close();
            } catch (Exception ex) {

            }
        }

    }

}
